package com.tjyx.common.util.db;

import java.lang.reflect.Field;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

import com.tjyx.common.TJYXException;
import com.tjyx.maintain.dto.UserInfo;

public class SqlUtil {

	/**
	 * 创建单表查询语句--查询所有
	 * @param sqlMap
	 * @return
	 * @throws TJYXException
	 */
	public static String createSql4SingleTable(String tableName,Map<String,Object> sqlMap) throws TJYXException {
		String sqlHead = "select * from ";
		return buildUpSelectSql4SingleTable(sqlHead,tableName, sqlMap);
	}
	
	/**
	 * 创建单表查询语句--查询记录总数
	 * @param sqlMap
	 * @return
	 * @throws TJYXException
	 */
	public static String createCounter4SingleTable(String tableName,Map<String,Object> sqlMap) throws TJYXException{
		String sqlHead = "select count(*) from ";
		return buildUpSelectSql4SingleTable(sqlHead,tableName,sqlMap);
	}
	
	/**
	 * 创建单表保存语句
	 * 
	 * @param tableName
	 * @param clazz
	 * @return
	 */
	public static String createSaveSql4SingleTable(String tableName,Class clazz) {
		String sqlHead = "insert into ";
		return buildUpSaveSql4SingleTable(sqlHead, tableName, clazz);
	}
	
	/**
	 * 创建单表更新语句
	 * @param tableName
	 * @param attrsMap
	 * @return
	 */
	public static String createUpdate4SingleTable(String tableName, Map<String,Object> attrsMap) {
		String sqlHead = "update ";
		return buildUpUpdateSql4SingleTable(sqlHead, tableName, attrsMap);
	}
	
	/**
	 * 构造单表查询语句
	 * 
	 * @param sqlHead
	 * @param sqlMap
	 * @return
	 * @throws TJYXException
	 */
	public static String buildUpSelectSql4SingleTable(String sqlHead, String tableName,Map<String,Object> sqlMap) throws TJYXException {
		StringBuilder orderBuilder = new StringBuilder();
		StringBuilder sqlBuilder = new StringBuilder(sqlHead);
		sqlBuilder.append(tableName);
		sqlBuilder.append(" as o ");
		Object[] keies = (Object[]) sqlMap.keySet().toArray();
		for(int i = 0; i < keies.length; i++) {
			if(i == 0) {
				sqlBuilder.append(" where");
			} else {
				sqlBuilder.append("and");
			}
			sqlBuilder.append(" o.");
			sqlBuilder.append(keies[i].toString());
			sqlBuilder.append("=? ");
		}
		sqlBuilder.append(orderBuilder);
		System.out.println(sqlBuilder.toString());
		return sqlBuilder.toString();
		
	}
	
	/**
	 * 构建单表的更新语句，用于插入操作
	 * @param sqlHead
	 * @param tableName
	 * @param clazz
	 * @return
	 */
	public static String buildUpSaveSql4SingleTable(String sqlHead,String tableName,Class clazz) {
		Field[] fields = clazz.getDeclaredFields();
		StringBuilder sqlBuilder = new StringBuilder(sqlHead);
		sqlBuilder.append(tableName + " (");
		StringBuilder paramsBuilder = new StringBuilder();
		for(int i = 0; i < fields.length; i++) {
			String fieldName = fields[i].getName();
			if(fieldName != "serialVersionUID") {
				if(i == fields.length - 1) {
					sqlBuilder.append(fieldName);
					paramsBuilder.append("?");
				} else {
					sqlBuilder.append(fieldName + ",");
					paramsBuilder.append("?,");
				}
			}
		}
		sqlBuilder.append(") values (");
		sqlBuilder.append(paramsBuilder);
		sqlBuilder.append(")");
		return sqlBuilder.toString();
		
	}
	
	public static String buildUpUpdateSql4SingleTable(String sqlHead,String tableName, Map<String,Object> attrsMap) {
		StringBuilder sqlBuilder = new StringBuilder(sqlHead);
		sqlBuilder.append(tableName);
		sqlBuilder.append(" as o set ");
		Object[] attrs = attrsMap.keySet().toArray();
		for(int i = 0; i < attrs.length; i++) {
			sqlBuilder.append(attrs[i].toString());
			if(i == attrs.length - 1) {
				sqlBuilder.append("=?");
			} else {
				sqlBuilder.append("=?,");
			}
		}
		sqlBuilder.append(" where o.id=?");
		return sqlBuilder.toString();
	}
	
	public static void main(String[] args) {
		System.out.println(createSaveSql4SingleTable("maintain_userInfo", UserInfo.class));
	}
}
